Custom Query (In Database)
(In-Database Processing)
Synopsis
Allows to run a custom query.Description
This operator allows to run a custom SQL query as a step in the process. The query may use multiple number of inputs. Use input1, input2, etc. in the query to refer to the inputs. In the SELECT expression, use t1, t2 aliases for referring to these inputs. Take a look at an example query for using multiple inputs:
SELECT t2.* FROM input1 JOIN input2 ON t1.a1 = t2.a1
Note that the metadata on the output port does not automatically reflect the expected result of the specified SQL query. By default, the metadata on the output port simply copies the metadata on the first input port. To change this, use the metadata parameter, and set the expected metadata manually, so that you can configure subsequent operators more easily. Alternatively, simply click on the Read Metadata button to populate this parameters by making the database parse the specified query. Note that you must click on this button every time you change the SQL code and expect to get different result back.
Input
example set input 1
Output
- example set output
Parameters
- query Custom SQL query to execute. The default query simply delivers the input data set on the output port. You may refer to any number of input data sets available using "input1", "input2", etc. Those strings are substituted by the input query during runtime. They get the alias "t1", "t2", respectively, that you can use in the column expressions. Note that you may need to use database specific quote characters when referring to case sensitive column names. Alternatively, you may use square brackets as well, they are automatically replaced by database specific quote characters, unless you uncheck the replace_square_brackets parameter.
- metadata Metadata that describes the expected output. As the result metadata of the custom query is not determined automatically, you can use this parameter to define the output metadata, and help configuring subsequent operators during design-time. Alternatively, hit the Read Metadata button next to the Edit List button to read the metadata from the database.
- replace square brackets Replace square brackets. By default square brackets are replaced in function expressions by the database specific characters used for escaping special identifiers (e.g. with backticks(`) for Google BigQuery). In some situations you may not want to replace the square brackets in the expression. Uncheck this parameter to do so.